﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

using MySql.Data.MySqlClient;

namespace MakeIT.MiraPark.Negocios
{
    public static partial class ModelBO
    {

        public static ArrayList getRankingMarcas(DateTime? dataIni, DateTime? dataFim)
        {
            string where = "WHERE ";
            StringBuilder query = new StringBuilder();
            query.AppendLine("SELECT v.IdMarca, ma.Marca, count(*) rank FROM movimentacao m ");
            query.AppendLine("   LEFT JOIN veiculo v ON m.Placa = v.Placa ");
            query.AppendLine("   LEFT JOIN marca ma ON v.IdMarca = ma.IdMarca ");
            if (dataIni != null)
            {
                query.AppendLine(where + "m.Entrada >= ?dataIni ");
                where = "AND ";
            }
            if (dataFim != null)
            {
                query.AppendLine(where + "m.Entrada <= ?dataFim ");
                where = "AND ";
            }
            query.AppendLine("GROUP BY IdMarca ");
            query.AppendLine("ORDER BY rank desc");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            if (dataIni != null) cmd.Parameters.AddWithValue("?dataIni",dataIni);
            if (dataFim != null) cmd.Parameters.AddWithValue("?dataFim", dataFim);

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                ArrayList lista = new ArrayList();

                while (reader.Read())
                {

                    if (!reader.IsDBNull(reader.GetOrdinal("Marca")) &&
                        !reader.IsDBNull(reader.GetOrdinal("rank")))
                    {
                        object[] item = new object[3];
                        item[0] = reader.GetInt32("IdMarca");
                        item[1] = reader.GetString("Marca");
                        item[2] = reader.GetInt32("rank");
                        lista.Add(item);
                    }
                }
                reader.Close();
                return lista;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }
        public static ArrayList getRankingModelos(int idMarca, DateTime? dataIni, DateTime? dataFim)
        {
            string where = "AND ";
            StringBuilder query = new StringBuilder();
            query.AppendLine("SELECT v.IdModelo, mo.Modelo, count(*) rank FROM movimentacao m ");
            query.AppendLine("   LEFT JOIN veiculo v ON m.Placa = v.Placa ");
            query.AppendLine("   LEFT JOIN modelo mo ON v.IdModelo = mo.IdModelo ");
            query.AppendLine("WHERE v.IdMarca=?IdMarca ");
            if (dataIni != null)
            {
                query.AppendLine(where + "m.Entrada >= ?dataIni ");
                where = "AND ";
            }
            if (dataFim != null)
            {
                query.AppendLine(where + "m.Entrada <= ?dataFim ");
                where = "AND ";
            }
            query.AppendLine("GROUP BY IdModelo ");
            query.AppendLine("ORDER BY rank desc");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdMarca", idMarca);
            if (dataIni != null) cmd.Parameters.AddWithValue("?dataIni", dataIni);
            if (dataFim != null) cmd.Parameters.AddWithValue("?dataFim", dataFim);

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                ArrayList lista = new ArrayList();

                while (reader.Read())
                {
                    object[] item = new object[3];

                    item[0] = reader.GetInt32("IdModelo");
                    if (!reader.IsDBNull(1))
                    {
                        item[1] = reader.GetString("Modelo");
                        item[2] = reader.GetInt32("rank");
                        lista.Add(item);
                    }
                }
                reader.Close();
                return lista;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }
        
    }
}
